from openpyxl import load_workbook
import pymysql.cursors

# 1.读取已有excel
wb = load_workbook('03门店服务数量.xlsx')
sheet = wb.active

data_list = []
for rc in sheet.rows:
    data = []
    for cell in rc:
        data.append(cell.value)
    data_list.append(data)

# 删除表头
del data_list[0]

#2.连接数据库
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='mysql',
                             db='car',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
try:

    with connection.cursor() as cursor:
        for data in data_list:
            sql = "INSERT INTO car_shop (name, num) VALUES (%s, %s)"
            cursor.execute(sql, data)

    connection.commit()
finally:
    connection.close()

print('导入成功')